from abc import ABC, abstractmethod
from typing import List
from io import BytesIO, TextIOWrapper
import base64

from urllib.parse import quote
from openpyxl import Workbook
import pandas as pd
import pydantic
import sqlalchemy as sa
from fastapi_pagination.bases import AbstractPage
from fastapi_pagination.ext.databases import paginate
from fastapi.responses import StreamingResponse
from fastapi import BackgroundTasks

from like.dependencies.database import db
from like.utils.urls import UrlUtil
from like.utils.array import ArrayUtil
from .models import hp_rules
from .schemas import (
    HpRulesListIn, HpRulesExportIn, HpRulesAddIn, HpRulesEditIn, HpRulesOut)
from .listsExcel import ListsExcel


class IHpRulesService(ABC):
    """首页规则抽象类"""

    @abstractmethod
    async def listBase(self, list_in: HpRulesListIn) -> sa.sql.Select:
        pass

    @abstractmethod
    async def list(self, list_in: HpRulesListIn) -> AbstractPage[HpRulesOut]:
        pass

    @abstractmethod    
    async def export_excel(self, export_in: HpRulesExportIn) -> str:
        pass

    @abstractmethod
    async def detail(self, id_: int) -> HpRulesOut:
        pass

    @abstractmethod
    async def add(self, add_in: HpRulesAddIn):
        pass

    @abstractmethod
    async def edit(self, edit_in: HpRulesEditIn):
        pass

    @abstractmethod
    async def delete(self, id_: int):
        pass


class HpRulesService(IHpRulesService):

    async def listBase(self, list_in: HpRulesListIn) -> sa.sql.Select:
        """首页规则基类"""
        columns = [
            hp_rules,
            
            ]
        where = []
        if list_in.sequence is not None and str(list_in.sequence):
            where.append(hp_rules.c.sequence == list_in.sequence)
        if list_in.sub_sequence is not None and str(list_in.sub_sequence):
            where.append(hp_rules.c.sub_sequence == list_in.sub_sequence)
        if list_in.category_id is not None and str(list_in.category_id):
            where.append(hp_rules.c.category_id == list_in.category_id)
        if list_in.types_id is not None and str(list_in.types_id):
            where.append(hp_rules.c.types_id == list_in.types_id)
        if list_in.source_id is not None and str(list_in.source_id):
            where.append(hp_rules.c.source_id == list_in.source_id)
        if list_in.short_content is not None and str(list_in.short_content):
            where.append(hp_rules.c.short_content.like(f'%{ list_in.short_content }%'))
        if list_in.comment is not None and str(list_in.comment):
            where.append(hp_rules.c.comment.like(f'%{ list_in.comment }%'))
        if list_in.regex is not None and str(list_in.regex):
            where.append(hp_rules.c.regex.like(f'%{ list_in.regex }%'))
        return sa.select(columns).where(*where) \
            .select_from(
                hp_rules
            ).order_by(hp_rules.c.id.asc())
    

    async def list(self, list_in: HpRulesListIn) -> AbstractPage[HpRulesOut]:
        """首页规则列表"""
        query = await self.listBase(list_in)
        res = await paginate(db, query)
        return res
    

    async def export_excel(self, export_in: HpRulesExportIn) -> str:
        """导出首页规则列表为 Excel 文件"""
        result = await self.listBase(export_in)

        # 计算要导出的记录范围
        offset = (export_in.page_start - 1) * export_in.page_size
        limit = (export_in.page_end - export_in.page_start + 1) * export_in.page_size

        # 修改查询以仅选择所需的记录
        result = result.offset(offset).limit(limit)

        query_result = await db.fetch_all(result)

        # 将查询结果转换为 Pandas DataFrame
        df = pd.DataFrame(query_result, columns=result.selected_columns.keys())

        # 将 DataFrame 转换为 Excel 文件的字节流
        excel_data = BytesIO()
        df.to_excel(excel_data, index=False)
        excel_data.seek(0)  # 将指针移回文件开头

        excel_bytes = excel_data.read()  # 读取字节流内容到字节字符串中
        return base64.b64encode(excel_bytes).decode('utf-8')  # 将字节字符串转换为Base64编码的字符串表示ArrayBuffer

        def iterfile():
            while True:
                data = excel_data.read(1024 * 1024)  # Read data in chunks of 1MB
                if not data:
                    break
                yield data

        # 返回 Excel 文件的字节流
        return StreamingResponse(
            # iter([excel_data.getvalue()]),
            iterfile(),
            media_type="application/vnd.openxmlformats-officedocument.spreadsheetml.sheet;charSet:UTF-8",
            headers={
                "Content-Disposition": f"attachment; filename*=UTF-8''{quote(export_in.file_name)}.xlsx"
            }
        )


    async def detail(self, id_: int) -> HpRulesOut:
        """首页规则详情"""
        model = await db.fetch_one(
            hp_rules.select().where(
                hp_rules.c.id == id_)
            .limit(1))
        assert model, '数据不存在!'
        res = HpRulesOut.from_orm(model)
        return res

    async def add(self, add_in: HpRulesAddIn):
        """首页规则新增"""
        await db.execute(hp_rules.insert().values({
            'sequence': add_in.sequence,
            'sub_sequence': add_in.sub_sequence,
            'category_id': add_in.category_id,
            'types_id': add_in.types_id,
            'source_id': add_in.source_id,
            'short_content': add_in.short_content,
            'comment': add_in.comment,
            'regex': add_in.regex,
        }))

    async def edit(self, edit_in: HpRulesEditIn):
        """首页规则编辑"""
        assert await db.fetch_one(
            hp_rules.select().where(
                hp_rules.c.id == edit_in.id)
            .limit(1)), '数据不存在!'
        await db.execute(
            hp_rules.update().where(hp_rules.c.id == edit_in.id).values({
                'sequence': edit_in.sequence,
                'sub_sequence': edit_in.sub_sequence,
                'category_id': edit_in.category_id,
                'types_id': edit_in.types_id,
                'source_id': edit_in.source_id,
                'short_content': edit_in.short_content,
                'comment': edit_in.comment,
                'regex': edit_in.regex,
            }))

    async def delete(self, id_: int):
        """首页规则删除"""
        assert await db.fetch_one(
            hp_rules.select().where(
                hp_rules.c.id == id_)
            .limit(1)), '数据不存在!'
        await db.execute(hp_rules.delete().where(hp_rules.c.id == id_))
        
    @classmethod
    async def instance(cls):
        """实例化"""
        return cls()
